using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SouthernLand.Common.BOL.Country;

namespace SouthernLand.Common.DAL
{
    public class CountryDAL : BaseDAL
    {
        #region ObjIDataReader

        /// <summary>
        /// Returns a new Country instance filled with the DataReader's current record data 
        /// </summary>
        private Country GetCountryFromReader(IDataReader dr)
        {
            var obj = new Country();
            obj.CountryID = (dr["CountryID"] is DBNull) ? Int32.MinValue : (Int32) dr["CountryID"];
            obj.CountryCode = (dr["CountryCode"] is DBNull) ? String.Empty : (String) dr["CountryCode"];
            obj.CountryName = (dr["CountryName"] is DBNull) ? String.Empty : (String) dr["CountryName"];
            return obj;
        }

        #endregion

        /// <summary>
        /// Returns a collection of Country with the data read from the input DataReader
        /// </summary>
        private List<Country> GetCountryCollectionFromReader(IDataReader dr)
        {
            var obj = new List<Country>();
            while (dr.Read())
                obj.Add(GetCountryFromReader(dr));
            return obj;
        }

        public int Insert(Country obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_Country_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = obj.CountryID;
                myCommand.Parameters.Add("@CountryCode", SqlDbType.Char).Value = obj.CountryCode;
                myCommand.Parameters.Add("@CountryName", SqlDbType.NVarChar).Value = obj.CountryName;

                return myCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Retrieves the Job_Country with the specified ID
        /// </summary>
        public Country GetCountryByID(Int32 CountryID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_Country_GetCountryByID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = CountryID;

                SqlDataReader dr = myCommand.ExecuteReader();
                return (dr.Read() ? GetCountryFromReader(dr) : null);
            }
        }

        //Get All Country
        public List<Country> GetAllCountry()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_Country_GetAllCountry", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetCountryCollectionFromReader(dr);
            }
        }
    }
}